USE [QLDL]
GO

/****** Object:  StoredProcedure [dbo].[sp_BaoCaoCongNo]    Script Date: 01/03/2011 15:26:23 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE procedure [dbo].[sp_BaoCaoCongNo]
	@Thang int, @Nam int
as
begin
	declare @NgayDauThang date
	set @NgayDauThang = CAST(@Thang as varchar) + '/01/' + CAST(@Nam as varchar)
	if not exists(select * from DAILY where NgayTiepNhan < @NgayDauThang)
		return 1
		
	create table #TEMP_TONGTIEN(MaDaiLy nvarchar(10), TongTienPXH money)
	insert into #TEMP_TONGTIEN(MaDaiLy, TongTienPXH)
				select MaDaiLy, 0 from DAILY where NgayTiepNhan < @NgayDauThang
	update #TEMP_TONGTIEN set TongTienPXH = (select SUM(TongTien) from PHIEUXUATHANG
											where (#TEMP_TONGTIEN.MaDaiLy = PHIEUXUATHANG.MaDaiLy) and (NgayLap < @NgayDauThang))
						from PHIEUXUATHANG
						where (#TEMP_TONGTIEN.MaDaiLy = PHIEUXUATHANG.MaDaiLy) and (NgayLap < @NgayDauThang)
	
	create table #TEMP_TONGTHU(MaDaiLy nvarchar(10), TongThu money)
	insert into #TEMP_TONGTHU(MaDaiLy, TongThu)
				select MaDaiLy, 0 from DAILY where NgayTiepNhan < @NgayDauThang
	update #TEMP_TONGTHU set TongThu = (select SUM(SoTienThu) from PHIEUTHUTIEN
										where (#TEMP_TONGTHU.MaDaiLy = PHIEUTHUTIEN.MaDaiLy) and (NgayThu < @NgayDauThang))
						from PHIEUTHUTIEN
						where (#TEMP_TONGTHU.MaDaiLy = PHIEUTHUTIEN.MaDaiLy) and (NgayThu < @NgayDauThang)

	create table #TEMP_CONGNO(Thang int, Nam int, MaDaiLy nvarchar(10), NoDau money, PhatSinh money, TienThu money, NoCuoi money)
	insert into #TEMP_CONGNO(Thang, Nam, MaDaiLy, NoDau, PhatSinh, TienThu, NoCuoi)
				select @Thang, @Nam, MaDaiLy, 0, 0, 0, 0
				from DAILY
				where (NgayTiepNhan < @NgayDauThang) or (MONTH(NgayTiepNhan) = @Thang and YEAR(NgayTiepNhan) = @Nam)
				
	update #TEMP_CONGNO set NoDau = TongTienPXH - TongThu
						from #TEMP_TONGTIEN, #TEMP_TONGTHU
						where (#TEMP_CONGNO.MaDaiLy = #TEMP_TONGTIEN.MaDaiLy) and 
							(#TEMP_CONGNO.MaDaiLy = #TEMP_TONGTHU.MaDaiLy)
							
	update #TEMP_CONGNO set PhatSinh = (select SUM(TongTien) from PHIEUXUATHANG
										where (#TEMP_CONGNO.MaDaiLy = PHIEUXUATHANG.MaDaiLy) and
											(MONTH(NgayLap) = @Thang) and (YEAR(NgayLap) = @Nam))
						from PHIEUXUATHANG
						where (#TEMP_CONGNO.MaDaiLy = PHIEUXUATHANG.MaDaiLy) and 
							(MONTH(NgayLap) = @Thang) and (YEAR(NgayLap) = @Nam)
							 
	update #TEMP_CONGNO set TienThu = (select SUM(SoTienThu) from PHIEUTHUTIEN
										where (#TEMP_CONGNO.MaDaiLy = PHIEUTHUTIEN.MaDaiLy) and
											(MONTH(NgayThu) = @Thang) and (YEAR(NgayThu) = @Nam))
						from PHIEUTHUTIEN
						where (#TEMP_CONGNO.MaDaiLy = PHIEUTHUTIEN.MaDaiLy) and
							(MONTH(NgayThu) = @Thang) and (YEAR(NgayThu) = @Nam)
							
	update #TEMP_CONGNO set NoCuoi = NoDau + PhatSinh - TienThu
				
	delete from CONGNO where (Thang = @Thang) and (Nam = @Nam)
		
	insert into CONGNO(Thang, Nam, MaDaiLy, NoDau, PhatSinh, TienThu, NoCuoi)
		select * from #TEMP_CONGNO
	
	return 0
end


GO


